﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Tab2Sql
 
{


    //TODO : The below all smells very bad. I've made some substantial design errors which mean most of the below needs to be restarted. 
    // - 2009-09-18 16:25:38
    /// <summary>
    ///
    /// </summary>
    public class SqlConvert
    {
        private Err error;
        private SqlVariants Source = new SqlVariants();
        private SqlVariants Destination = new SqlVariants();
        private StringBuilder endResult;
        /// <summary>
        /// Final string.
        /// </summary>
        public StringBuilder EndResult
        {
            get
            {
                if (error.Status == 0) { return endResult; }
                else { return error.ErrorLog; }
            }
            set { endResult = value; }
        }
        private StringBuilder startStatement;
        private Dictionary<string, string> syntax;

		
        /// <summary>
        ///
        /// </summary>
        /// <param name="From">
        /// A <see cref="SqlVariants"/>
        /// </param>
        /// <param name="To">
        /// A <see cref="SqlVariants"/>
        /// </param>
        /// <param name="sqlStatement">
        /// A <see cref="System.String"/>
        /// </param>
        public SqlConvert (SqlVariants From, SqlVariants To, string sqlStatement)
        {
            error = new Err ("SqlConvert");
            Source = From;
            Destination = To;
            startStatement = new StringBuilder (sqlStatement.Length);
            EndResult = new StringBuilder (sqlStatement.Length);
            startStatement.Append (sqlStatement);

            if (From == SqlVariants.TSql && To == SqlVariants.MSAccess)
            {
                syntax = fromTSql2Access ();
            }
            if (From == SqlVariants.SqlAnywhere && To == SqlVariants.TSql)
            {
                syntax = fromSqlAnywhere2TSql ();
            }
            if (From == SqlVariants.SqlAnywhere && To == SqlVariants.Sqlite)
            {
                syntax = fromSqlAnywhere2Sqlite ();
            }
            if (From == SqlVariants.TSql && To == SqlVariants.SqlAnywhere)
            {
                syntax = fromTSql2SqlAnywhere ();
            }
            if (From == SqlVariants.TSql && To == SqlVariants.Sqlite)
            {
                syntax = fromTSql2SQLite ();
            }
            if (From == SqlVariants.TSql && To == SqlVariants.Informix)
            {
                syntax = fromTSql2Informix ();
            }
            // If syntax is not instantiated by this point then arguably the program should generate an exception and 
            // close gracefully. At present (2011-01-28) it merely crashes so I'm adding this junk here.
            if (syntax == null)
            {
                syntax = new Dictionary<string, string> ();
                syntax.Add ("somethingorother", "");
    
			}

            
        }
        /// <summary>
        ///
        /// </summary>
        public void Translate()
        {
            string tmp = startStatement.ToString();
            
            foreach (var foo in syntax)
            {
                string from = foo.Key;
                string to = foo.Value;
                try 
                {
                tmp = Regex.Replace(tmp, from, to,RegexOptions.IgnoreCase);
                }
                catch (ArgumentException ex)
                {
                    error.Add(ex.ParamName);
                    error.Add(ex.Message);
                    error.Status=1;
                }

            }
            EndResult.Append(tmp);
        }


        private Dictionary<string, string> fromTSql2Informix()
        {
            syntax = new Dictionary<string, string>();
            syntax.Add(@"SET NOCOUNT (ON|OFF)(;?)",string.Empty);
            syntax.Add(@"FROM #", @"FROM  ");
            syntax.Add(@"SET DATEFORMAT DMY(;?)",string.Empty);
            syntax.Add(@"BEGIN TRY(.*)END CATCH", string.Empty);
            syntax.Add(@"BEGIN TRANSACTION", string.Empty);
            syntax.Add(@"COMMIT", string.Empty);
            syntax.Add(@"BEGIN TRY(\s|\n)*DROP TABLE #foo;(\s|\n)*END TRY(\s|\n)*BEGIN CATCH(\s|\n)*PRINT 'Drop failed \(continuing\)';(\s|\n)*END CATCH(\s|\n)*GO", string.Empty);
            syntax.Add(@"BEGIN TRY\s*DROP TABLE #foo;\s*END TRY\s*BEGIN CATCH\s*PRINT 'Drop failed (continuing)';\s*END CATCH",string.Empty);
            syntax.Add(@"if exists(.*)BEGIN(.*)END", string.Empty);
            syntax.Add(@"TOP (\d+) (PERCENT)?", string.Empty);
            syntax.Add(@"INSERT INTO #", @"INSERT INTO ");
            syntax.Add(@"CREATE TABLE #","CREATE TEMP TABLE ");
            syntax.Add(@"DATETIME",@"DATE");
            string tmpGUID = System.Guid.NewGuid().ToString();
            syntax.Add(@"\);\nGO", tmpGUID);
            syntax.Add(@"(GO(\s|\n)+GO)+", "\n");
            syntax.Add(@"\[", "");
            syntax.Add(@"'\nUNION ALL", "' FROM sysmaster:dual d1\nUNION ALL'");
            syntax.Add(@"\]", "");
            syntax.Add(@"GO\n", "\n");
            syntax.Add(tmpGUID, ");\nGO\n");
            syntax.Add(@"\n{2,}", "\n");
            syntax.Add(@"^\n{1,}", String.Empty);
            syntax.Add(@"([1][0-9]{3}|[2][0-9]{3})-([1][0-2]|[0][1-9]|[1-9])-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9]) ([0-2][0-9]:[0-5][0-9]:[0-9][0-9]\.[0-9][0-9][0-9])","$3/$2/$1");
            return syntax;

        }


        private Dictionary<string, string> fromSqlAnywhere2TSql ()
        {
        	syntax = new Dictionary<string, string> ();

            syntax.Add (@"current user", "current_user");
        	syntax.Add (@"current timestamp", "current_timestamp");
        	syntax.Add (@"long(\s)+varchar", "varchar(max)");
        	syntax.Add (@" double ", " real ");
        	syntax.Add (@"long(\s)+ varbinary", "varbinary(max)");
        	syntax.Add (@"if(.*)then[\s]+$", "if $1 ");
        	syntax.Add (@"||", "+");
        	syntax.Add (@"if(.+)then(.+)else(.+)endif", "case when $1 then $2 else $3 end");
        	syntax.Add (@"endif", string.Empty);
        	syntax.Add (@"COMMENT ON COLUMN ", "--COMMENT ON COLUMN ");
        	syntax.Add (@"""DBA""", @"""DBO""");
        	syntax.Add (@"DBA\.", @"DBO.");
        	syntax.Add (@"IN ""SYSTEM"";", String.Empty);
        	return syntax;
        }
		
		private Dictionary<string, string> fromSqlAnywhere2Sqlite ()
		{
			syntax = new Dictionary<string, string> ();
			syntax.Add (@"BEGIN\n", "BEGIN TRANSACTION;\n");
			syntax.Add (@"INSERT INTO #", @"INSERT INTO ");
			syntax.Add (@"CREATE TABLE #", "CREATE TEMP TABLE ");
			syntax.Add (@"DROP TABLE IF EXISTS #", "DROP TABLE IF EXISTS ");
			syntax.Add (@"END;", string.Empty);
			return syntax;
		}
		
		
		
        private Dictionary<string, string> fromTSql2SqlAnywhere()
        {
            syntax = new Dictionary<string, string>();

            syntax.Add(@"current_user","current user");
            syntax.Add(@"current_timestamp","current timestamp" );
            syntax.Add("varchar(max)",@"long(\s)+varchar");
            syntax.Add(" real ",@" double ");
            syntax.Add("SET NOCOUNT ON;", String.Empty);
            syntax.Add("SET NOCOUNT OFF", String.Empty);
            syntax.Add("SET DATEFORMAT DMY;", String.Empty);
            syntax.Add("SELECT TOP 10 PERCENT", "SELECT ");
            string moo = @"if.exists.*select.*from.*tempdb.*dbo.*sysobjects.*where.+o.+xtype.*N.tempdb..#foo.\)\)";
            string moo2=@"BEGIN\n[\s]+BEGIN.TRY\n[\s]+DROP.TABLE.#foo.*\n[\s]+END.TRY\n[\s]+BEGIN.CATCH\n[\s]+PRINT..Drop.Failed.\n[\s]+END.CATCH\nEND\nGO";
            syntax.Add(moo, string.Empty);
            syntax.Add(moo2, string.Empty);
            //moo= @"IF EXISTS .SELECT 1 FROM INFORMATION_SCHEMA.TABLES(.+)";
            
            syntax.Add(@"\n{2,}", "\n");
            syntax.Add(@"GO\nGO", "GO\n");
            syntax.Add(@"^[\n\s]+GO[\n\s]+CREATE", "CREATE");
            return syntax;
        }
		private Dictionary<string, string> fromMySQL2TSql()
        {
            syntax = new Dictionary<string, string>();
			syntax.Add(@"`", "\"");
            syntax.Add(@"current user", "current_user");
            syntax.Add(@"DEFAULT NULL", string.Empty);
            syntax.Add(@"DEFAULT NOT NULL", string.Empty);
            syntax.Add(@"DEFAULT 'current_timestamp'", "DEFAULT (current_timestamp)");
            syntax.Add(@"current timestamp", "current_timestamp");
            syntax.Add(@"long(\s)+varchar", "varchar(max)");
            syntax.Add(@" double ", " real ");
            syntax.Add(@"long(\s)+ varbinary", "varbinary(max)");
            syntax.Add(@"if(.*)then(.*)else(.*)endif", @"case when \1 then \2 else \3 end");
            syntax.Add(@"COMMENT ON COLUMN ", "--COMMENT ON COLUMN ");
            syntax.Add(@"""DBA""", @"""DBO""");
            syntax.Add(@"IN ""SYSTEM"";", String.Empty);
            return syntax;
        }
        private Dictionary<string, string> fromTSql2SQLite()
        {
            syntax = new Dictionary<string, string>();
            syntax.Add(@"SET NOCOUNT (ON|OFF)(;?)", string.Empty);
            syntax.Add(@"FROM #", @"FROM  ");
            syntax.Add(@"SET DATEFORMAT DMY(;?)", string.Empty);
            syntax.Add(@"BEGIN TRY(.*)END CATCH", string.Empty);
            syntax.Add(@"BEGIN TRANSACTION", string.Empty);
            syntax.Add(@"COMMIT", string.Empty);
            syntax.Add(@"BEGIN TRY(\s|\n)*DROP TABLE #foo;(\s|\n)*END TRY(\s|\n)*BEGIN CATCH(\s|\n)*PRINT 'Drop failed \(continuing\)';(\s|\n)*END CATCH(\s|\n)*GO", string.Empty);
            syntax.Add(@"BEGIN TRY\s*DROP TABLE #foo;\s*END TRY\s*BEGIN CATCH\s*PRINT 'Drop failed (continuing)';\s*END CATCH", string.Empty);
            syntax.Add(@"if exists(.*)BEGIN(.*)END", string.Empty);
            syntax.Add(@"TOP (\d+) (PERCENT)?", string.Empty);
            syntax.Add(@"INSERT INTO #", @"INSERT INTO ");
            syntax.Add(@"CREATE TABLE #", "CREATE TEMP TABLE ");
            syntax.Add(@"(GO(\s|\n)+GO)+", "\n");
            syntax.Add(@"\[", "");
            syntax.Add(@"\]", "");
            syntax.Add(@"PRINT ", "--PRINT");
            syntax.Add(@"GO\n", "\n");
            syntax.Add(@"\n{2,}", "\n");
            syntax.Add(@"([a-zA-Z]|[0-9])\n\/\*", "$1;\n/*");
            syntax.Add(@"^\n{1,}", String.Empty);
            syntax.Add(@"([1][0-9]{3}|[2][0-9]{3})-([1][0-2]|[0][1-9]|[1-9])-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9]) ([0-2][0-9]:[0-5][0-9]:[0-9][0-9]\.[0-9][0-9][0-9])", "$3/$2/$1");
            syntax.Add("-- SqlVariant : TSql", "-- SqlVariant : SQLite");
            syntax.Add(@"((\r\n|\n)+\s?)", Environment.NewLine);
            return syntax;
        }


        //IN "SYSTEM";
       











        private Dictionary<string, string> fromTSql2Access()
        {
            syntax = new Dictionary<string, string>();
            syntax.Add(@"\[char\]\(\d+\)", "TEXT");
            syntax.Add(@"\[varchar\]\(\d+\)", "TEXT");
            syntax.Add(@"\[int\]", "NUMBER");
            syntax.Add(@"\[numeric\]\(\d+,\s\d+\)", "NUMBER");
            syntax.Add(@"\[smallint\]", "NUMBER");
            syntax.Add(@"\[datetime\]", "DATETIME");
            syntax.Add(@"\nGO\s", String.Empty);
            syntax.Add(@"ON\s\[PRIMARY\]", String.Empty);
            syntax.Add(@"\[dbo\]\.", String.Empty);
            return syntax;
        }




    }
    /// <summary>
    ///
    /// </summary>
    class sqlTranslate
    {
        /// <summary>
        /// Removes basic T-SQL only features.
        /// This is a really stupid way of doing it and this will be refactored completely as soon as I get the chance.
        /// (Comment written May 6th 2009)
        /// </summary>
        /// <param name="statements"></param>
        /// <returns></returns>
        public static StringBuilder RemoveTSQL(StringBuilder statements)
        {
            StringBuilder newSB = new StringBuilder(statements.Length);
            newSB.Append(makeSqlSafe(statements.ToString()));
            return newSB;
        }


        private static string makeSqlSafe(string inputString)
        {
            string resultString = inputString.Replace("[", String.Empty);
            resultString = dataTypes(resultString);
            resultString = changeDateFormats(resultString, true);
            resultString = resultString.Replace("]", String.Empty);
            Regex r = new Regex(@";\s*;");
            resultString = r.Replace(resultString, ";");
            resultString = resultString.TrimStart();
            return resultString;
        }

        private static string dataTypes(string input)
        {
            string pattern = @"CREATE\sTABLE\s#";
            string replace = @"CREATE TEMP TABLE ";
            string s = Regex.Replace(input, pattern, replace);

            pattern = @"DATETIME";
            replace = @"DATE";

            s = Regex.Replace(s, pattern, replace);

            pattern = @"INSERT INTO #";
            replace = @"INSERT INTO ";

            s = Regex.Replace(s, pattern, replace);

            pattern = @"BEGIN TRY(.*)END CATCH";
            replace = String.Empty;

            s = Regex.Replace(s, pattern, replace, RegexOptions.Singleline);

            pattern = @"SET NOCOUNT ON;";
            replace = String.Empty;
            s = Regex.Replace(s, pattern, replace);

            pattern = @"FROM #";
            replace = @"FROM  ";
            s = Regex.Replace(s, pattern, replace);

            pattern = @"TOP \d+";
            replace = String.Empty;
            s = Regex.Replace(s, pattern, replace);

            pattern = @"\n\n";
            replace = Environment.NewLine;
            s = Regex.Replace(s, pattern, replace);

            pattern = @"SET DATEFORMAT DMY;";
            replace = String.Empty;
            s = Regex.Replace(s, pattern, replace);
            return s;
        }

        // this is very crude
        private static string changeDateFormats(string input)
        {
            string YYYYMMDD =
                @"([1][0-9]{3}|[2][0-9]{3})-([1][0-2]|[0][1-9]|[1-9])-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9])";

            string replace = @"$2/$3/$1";
            string s = Regex.Replace(input, YYYYMMDD, replace);
            return s;
        }

        private static string changeDateFormats(string input, bool EliminateTime)
        {
            string YYYYMMDD =
                @"([1][0-9]{3}|[2][0-9]{3})-([1][0-2]|[0][1-9]|[1-9])-([3][0-1]|[2][0-9]|[1][0-9]|[0][1-9]) ([0-2][0-9]:[0-5][0-9]:[0-9][0-9]\.[0-9][0-9][0-9])";

            string replace = @"$2/$3/$1";
            string s = Regex.Replace(input, YYYYMMDD, replace);
            return s;
        }

        private static string eliminateTime(string input)
        {
            string timeString = "00:00:00.000";
            string replace = String.Empty;
            string s = Regex.Replace(input, timeString, replace);
            return s;
        }
    }
}
